﻿Imports System.Data.SqlClient
Public Class FrmTimDonViHanhChinh
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiDat2()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD2.DisplayMember = "kyhieu"
        CmbLD2.ValueMember = "kyhieu"
        CmbLD2.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD1.DisplayMember = "kyhieu"
        CmbLD1.ValueMember = "kyhieu"
        CmbLD1.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiBD.DisplayMember = "TenLoaiBD"
        CmbLoaiBD.ValueMember = "maBD"
        CmbLoaiBD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub CmbHuyen_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmbHuyen.SelectedIndexChanged
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub DataGridViewBD_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewBD.CellClick
        'TextMaBDid.DataBindings.Clear()
        'TextMaBDid.DataBindings.Add("Text", DataGridViewBD.DataSource, "maBDid")
        ChuSD1.DataBindings.Clear()
        ChuSD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "chusd1")
        DateNgayBD.DataBindings.Clear()
        DateNgayBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "NgayBD")
        TextToBD1.DataBindings.Clear()
        TextToBD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "tobd")
        TextSoThua1.DataBindings.Clear()
        TextSoThua1.DataBindings.Add("Text", DataGridViewBD.DataSource, "sothua1")
        TextDienTich1.DataBindings.Clear()
        TextDienTich1.DataBindings.Add("Text", DataGridViewBD.DataSource, "dientich1")
        TextNoiDungBD.DataBindings.Clear()
        TextNoiDungBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "noidungbd")
        'CmbHuyen.DataBindings.Clear()
        'CmbHuyen.DataBindings.Add("Text", DataGridViewBD.DataSource, "T_huyen")
        'CmbXa.DataBindings.Clear()
        'CmbXa.DataBindings.Add("Text", DataGridViewBD.DataSource, "Tenxa")
        TextDiaChiTruoc.DataBindings.Clear()
        TextDiaChiTruoc.DataBindings.Add("Text", DataGridViewBD.DataSource, "cho_o1")
        CmbLD1.DataBindings.Clear()
        CmbLD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "loaidat1id")
        CmbLD2.DataBindings.Clear()
        CmbLD2.DataBindings.Add("Text", DataGridViewBD.DataSource, "loaidat2id")
        TextSoThua2.DataBindings.Clear()
        TextSoThua2.DataBindings.Add("Text", DataGridViewBD.DataSource, "sothua2")
        TextDienTich2.DataBindings.Clear()
        TextDienTich2.DataBindings.Add("Text", DataGridViewBD.DataSource, "dientich2")
        CmbLoaiBD.DataBindings.Clear()
        CmbLoaiBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenloaibd")
        cmbPhong.DataBindings.Clear()
        cmbPhong.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenphong")
        CmbNgan.DataBindings.Clear()
        CmbNgan.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenngan")
        CmbKe.DataBindings.Clear()
        CmbKe.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenke")
        CmbKho.DataBindings.Clear()
        CmbKho.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenkho")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewBD.DataSource, "tentinhtrang")

    End Sub

    Private Sub DataGridViewBD_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewBD.CellContentClick

    End Sub

    Private Sub FrmTimThongTin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        NapDonViHuyen()
        NapLoaiDat1()
        NapLoaiDat2()
        NapLoaiBienDong()
        TextBoxNam.Text = Year(Today())
        Tat_chuc_nang()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        DataGridViewBD.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBD.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub
    Private Sub Tat_chuc_nang()
        Me.ChuSD1.Enabled = False
        Me.TextDiaChiTruoc.Enabled = False
        Me.TextToBD1.Enabled = False
        Me.TextSoThua1.Enabled = False
        Me.TextDienTich1.Enabled = False
        Me.CmbLD1.Enabled = False
        Me.TextSoThua2.Enabled = False
        Me.TextDienTich2.Enabled = False
        Me.CmbLD2.Enabled = False
        Me.TextNoiDungBD.Enabled = False

        Me.DataGridViewBD.Enabled = False
        Me.DateNgayBD.Enabled = False
        Me.CmbLoaiBD.Enabled = False
    End Sub
    Private Sub Bat_chuc_nang()
        Me.ChuSD1.Enabled = True
        Me.TextDiaChiTruoc.Enabled = True
        Me.TextToBD1.Enabled = True
        Me.TextSoThua1.Enabled = True
        Me.TextDienTich1.Enabled = True
        Me.CmbLD1.Enabled = True
        Me.TextSoThua2.Enabled = True
        Me.TextDienTich2.Enabled = True
        Me.CmbLD2.Enabled = True
        Me.TextNoiDungBD.Enabled = True

        Me.DataGridViewBD.Enabled = True
        Me.DateNgayBD.Enabled = True
        Me.CmbLoaiBD.Enabled = True
    End Sub
    Private Sub cleathongtin()
        ChuSD1.Text = ""
        ChuSD1.Text = ""
        TextDiaChiTruoc.Text = ""
        TextToBD1.Text = ""
        TextSoThua1.Text = ""
        TextDienTich1.Text = ""
        TextSoThua2.Text = ""
        TextDienTich2.Text = ""
        TextNoiDungBD.Text = ""
    End Sub
    Private Sub NapDataGridViewDonViHanhChinh()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  maBDid, bd.ngayBD, bd.ChuSD1,bd.cho_o1, " &
                                " bd.toBD, bd.sothua1,bd.dientich1, bd.loaidat1id, bd.NoiDungBD, " &
                                " bd.sothua2,bd.dientich2, " &
                                " bd.loaidat2id, lbd.tenloaibd , p.tenphong, ng.tenngan, k.tenke, kh.tenkho, " &
                                " tt.TenTinhTrang From DMTinhTrang tt, DMPhong p, DMNgan ng, DMKe k, DMKho kh,loaiBD lbd," &
                                " BIENDONG bd, tenxa tx, tenhuyen th " &
                                " WHERE bd.loaibdid = lbd.mabd and diachihuyenid= '" & CmbHuyen.SelectedValue.ToString & "'" &
                                " and diachixaid= '" & CmbXa.SelectedValue.ToString & "'and bd.diachixaid = tx.maxa " &
                                " and bd.diachihuyenid= th.mah and bd.maphongID = p.maphongid and bd.manganid = ng.manganid " &
                                " and bd.makeid = k.makeid and bd.makhoid = kh.makhoid and bd.tinhTrangid= tt.matinhtrangid " &
                                " and  year(bd.ngayBD) = '" & Format(TextBoxNam.Text) & "' order by ngaybd ", ob_cnn) '
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBD.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("không có thông tin ")
            Return
        End If
        ob_cnn.Close()
    End Sub
    Private Sub XuatExcel()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "So Bien Dong"
                '---------------
                .Cells.Columns.AutoFit()
                .Cells.Columns.Font.Name = "Times New Roman"
                .Cells.Select()
                .Cells.EntireColumn.AutoFit()
                .Cells(1, 1).Select()
                '------------
                Dim i As Integer = 1
                For col = 0 To DataGridViewBD.Columns.Count - 1
                    .cells(1, i).value = DataGridViewBD.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewBD.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewBD.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewBD.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất sang Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub

    Private Sub Buttonlamtuoi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Buttonlamtuoi.Click
        cleathongtin()
        NapDataGridViewDonViHanhChinh()
        Bat_chuc_nang()
    End Sub
    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        'If MsgBox("Bạn có muốn thoát khỏi chương trình không ?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Thoát chương trình") = MsgBoxResult.No Then
        '    e.Cancel = True
        'End If
    End Sub

    Private Sub ButtonThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewBD_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBD.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBD.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBD.RowHeadersWidth < Size.Width + 20 Then DataGridViewBD.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        XuatExcel()

    End Sub

End Class